﻿#include "userdatacon.h"
#include <QtDebug>

userDataCon::userDataCon()
{
    if (QSqlDatabase::contains("qt_sql_default_connection"))
    {
        m_qSqlDataBase = QSqlDatabase::database("qt_sql_default_connection");
    }
    else
    {
        m_qSqlDataBase = QSqlDatabase::addDatabase("QSQLITE");
        m_qSqlDataBase.setDatabaseName("QChatUserDate.db");
        m_qSqlDataBase.setUserName("qchater");
        m_qSqlDataBase.setPassword("123");
    }
}

//-- create table qchartTable (id int primary key, name varchar(30), ip string, port string, context text)
//-- SELECT * FROM qchartTable
//-- INSERT INTO qchartTable VALUES (1, "user1", "10.0.0.1", "10086", "hello I send to many things")
//-- SELECT * FROM qchartTable
//-- UPDATE qchartTable SET context="hahahaha"||context WHERE name="user1"
//-- SELECT * FROM qchartTable

void userDataCon::create()
{
    if (!m_qSqlDataBase.open()){
        qDebug() << "Error: Failed to connect database." << m_qSqlDataBase.lastError();
    }
    else
    {
        QSqlQuery m_qSqlQuery(m_qSqlDataBase);
        QString create_sql = "create table student (id int primary key, name varchar(30), age int)";
        m_qSqlQuery.prepare(create_sql);
        if(!m_qSqlQuery.exec())
        {
            qDebug() << "Error: Fail to create table." << m_qSqlQuery.lastError();
        }
        else
        {
            qDebug() << "Table created!";
        }
        m_qSqlQuery.finish();

    }
}

void userDataCon::close()
{
    m_qSqlDataBase.close();
}


void userDataCon::insert()
{
    QSqlQuery m_qSqlQuery(m_qSqlDataBase);
    QString insert_sql = "insert into student values (?, ?, ?)";
    m_qSqlQuery.prepare(insert_sql);
    m_qSqlQuery.addBindValue(1);
    m_qSqlQuery.addBindValue("Wang");
    m_qSqlQuery.addBindValue(25);
    if(!m_qSqlQuery.exec())
    {
        qDebug() << m_qSqlQuery.lastError();
    }
    else
    {
        qDebug() << "inserted Wang!";
    }
    if(!m_qSqlQuery.exec("INSERT INTO student VALUES(2, \"Li\", 23)"))
    {
        qDebug() << m_qSqlQuery.lastError();
    }
    else
    {
        qDebug() << "inserted Li!";
    }
    m_qSqlQuery.finish();
}

void userDataCon::change()
{
    QSqlQuery m_qSqlQuery(m_qSqlDataBase);
    QString update_sql = "update student set name = :name where id = :id";
    m_qSqlQuery.prepare(update_sql);
    m_qSqlQuery.bindValue(":name", "Qt");
    m_qSqlQuery.bindValue(":id", 1);
    if(!m_qSqlQuery.exec())
    {
        qDebug() << m_qSqlQuery.lastError();
    }
    else
    {
        qDebug() << "updated!";
    }
    m_qSqlQuery.finish();

}

void userDataCon::find()
{
    QSqlQuery m_qSqlQuery(m_qSqlDataBase);
    QString select_sql = "select id, name from student";
    if(!m_qSqlQuery.exec(select_sql))
    {
        qDebug()<<m_qSqlQuery.lastError();
    }
    else
    {
        while(m_qSqlQuery.next())
        {
            int id = m_qSqlQuery.value(0).toInt();
            QString name = m_qSqlQuery.value(1).toString();
            qDebug()<<QString("id:%1    name:%2").arg(id).arg(name);
        }
    }
    m_qSqlQuery.finish();

}

void userDataCon::findall()
{
    QSqlQuery m_qSqlQuery(m_qSqlDataBase);
    QString select_all_sql = "select * from student";
    m_qSqlQuery.prepare(select_all_sql);
    if(!m_qSqlQuery.exec())
    {
        qDebug()<<m_qSqlQuery.lastError();
    }
    else
    {
        while(m_qSqlQuery.next())
        {
            int id = m_qSqlQuery.value(0).toInt();
            QString name = m_qSqlQuery.value(1).toString();
            int age = m_qSqlQuery.value(2).toInt();
            qDebug()<<QString("id:%1    name:%2    age:%3").arg(id).arg(name).arg(age);
        }
    }
    m_qSqlQuery.finish();

}

void userDataCon::findmaxid()
{
    QSqlQuery m_qSqlQuery(m_qSqlDataBase);
    QString select_max_sql = "select max(id) from student";
    int max_id = 0;
    m_qSqlQuery.prepare(select_max_sql);
    if(!m_qSqlQuery.exec())
    {
        qDebug() << m_qSqlQuery.lastError();
    }
    else
    {
        while(m_qSqlQuery.next())
        {
            max_id = m_qSqlQuery.value(0).toInt();
            qDebug() << QString("max id:%1").arg(max_id);
        }
    }
    m_qSqlQuery.finish();

}

void userDataCon::remove()
{
    QSqlQuery m_qSqlQuery(m_qSqlDataBase);
    QString delete_sql = "delete from student where id = ?";
    m_qSqlQuery.prepare(delete_sql);
    m_qSqlQuery.addBindValue(0);
    if(!m_qSqlQuery.exec())
    {
        qDebug()<<m_qSqlQuery.lastError();
    }
    else
    {
        qDebug()<<"deleted!";
    }
    m_qSqlQuery.finish();

}

void userDataCon::clear()
{
    QSqlQuery m_qSqlQuery(m_qSqlDataBase);
    QString clear_sql = "delete from student";
    m_qSqlQuery.prepare(clear_sql);
    if(!m_qSqlQuery.exec())
    {
        qDebug() << m_qSqlQuery.lastError();
    }
    else
    {
        qDebug() << "table cleared";
    }
    m_qSqlQuery.finish();

}
